from backend.api.connection import POOL
# from connection import POOL   # 单独运行用这个
import datetime


# 新增题目
# 返回
# {'exid': 0, 'ok': 0, 'msg': ''}
def addExercise(word, question, choice_A, choice_B, choice_C, choice_D, answer, creator):
    conn = POOL.connection()
    cur = conn.cursor()
    response = {'exid': 0, 'ok': 0, 'msg': ''}

    dt = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")

    sql = """INSERT INTO exerciselist (
            word, question,
            choice_A, choice_B, choice_C, choice_D,
            answer, creator,
            create_time,edit_time,
            approve) VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"""
    cur.execute(sql, (word,
                      question,
                      choice_A,
                      choice_B,
                      choice_C,
                      choice_D,
                      answer,
                      creator,
                      dt, dt,
                      0))

    exid = int(cur.lastrowid)
    conn.commit()
    cur.close()
    response['exid'] = exid
    response['ok'] = 1
    return response

# 下载习题(按计划)
# 返回
# {'data': [{'exid': 0, 'word': '', 'question': '',
#                'choice_A': '', 'choice_B': '', 'choice_C': '', 'choice_D': '',
#                'answer': '', 'creator': 0, 'username': '',
#                'create_time': "", 'edit_time': "", 'approve': 0}
#           ],
#  'ok': 1,
#  'msg':''}


def loadExercise(pid):
    conn = POOL.connection()
    cur = conn.cursor()
    response = {'data': [], 'ok': 0, 'msg': ''}

    sql = '''SELECT n.exid, n.word, n.question,
            n.choice_A, n.choice_B,n.choice_C, n.choice_D, n.answer,
            n.creator, n.create_time, n.edit_time,
            n.approve, u.username FROM exerciselist n, userinfo u, plan_{} p
            WHERE n.approve = 1 AND u.uid = n.creator
            AND n.word = p.word'''.format(pid)
    cur.execute(sql)
    resultset = cur.fetchall()
    data = []
    # print(resultset)
    for result in resultset:
        note = {'exid': 0, 'word': '', 'question': '',
                'choice_A': '', 'choice_B': '', 'choice_C': '', 'choice_D': '',
                'answer': '', 'creator': 0, 'username': '',
                'create_time': "", 'edit_time': "", 'approve': 0}
        note['exid'] = result[0]
        note['word'] = result[1]
        note['question'] = result[2]
        note['choice_A'] = result[3]
        note['choice_B'] = result[4]
        note['choice_C'] = result[5]
        note['choice_D'] = result[6]
        note['answer'] = result[7]
        note['creator'] = result[8]
        note['username'] = result[12]
        note['create_time'] = datetime.datetime.strftime(
            result[9], '%Y-%m-%d %H:%M:%S')
        note['edit_time'] = datetime.datetime.strftime(
            result[10], '%Y-%m-%d %H:%M:%S')
        note['approve'] = result[11]
        data.append(note)

    response['data'] = data
    response['ok'] = 1
    # print(response)
    return response


# 获取未审核习题
# 返回
# {'data': [{'exid': 0, 'word': '', 'question': '',
#                'choice_A': '', 'choice_B': '', 'choice_C': '', 'choice_D': '',
#                'answer': '', 'creator': 0, 'username': '',
#                'create_time': "", 'edit_time': "", 'approve': 0}
#           ],
#  'ok': 1,
#  'msg':''}
def getUnCheckedExercise():
    conn = POOL.connection()
    cur = conn.cursor()
    response = {'data': [], 'ok': 0, 'msg': ''}

    sql = '''SELECT n.exid, n.word, n.question,
            n.choice_A, n.choice_B,n.choice_C, n.choice_D, n.answer,
            n.creator, n.create_time, n.edit_time,
            n.approve, u.username FROM exerciselist n, userinfo u
            WHERE n.approve = 0 AND u.uid = n.creator'''
    cur.execute(sql)
    resultset = cur.fetchall()
    data = []
    # print(resultset)
    for result in resultset:
        note = {'exid': 0, 'word': '', 'question': '',
                'choice_A': '', 'choice_B': '', 'choice_C': '', 'choice_D': '',
                'answer': '', 'creator': 0, 'username': '',
                'create_time': "", 'edit_time': "", 'approve': 0}
        note['exid'] = result[0]
        note['word'] = result[1]
        note['question'] = result[2]
        note['choice_A'] = result[3]
        note['choice_B'] = result[4]
        note['choice_C'] = result[5]
        note['choice_D'] = result[6]
        note['answer'] = result[7]
        note['creator'] = result[8]
        note['username'] = result[12]
        note['create_time'] = datetime.datetime.strftime(
            result[9], '%Y-%m-%d %H:%M:%S')
        note['edit_time'] = datetime.datetime.strftime(
            result[10], '%Y-%m-%d %H:%M:%S')
        note['approve'] = result[11]
        data.append(note)

    response['data'] = data
    response['ok'] = 1
    # print(response)
    return response


# 审核习题
# 返回
# {
#  'ok': 1,
#  'msg':''}
def checkExercise(exid,approve):
    conn = POOL.connection()
    cur = conn.cursor()
    response = {'ok': 0, 'msg': ''}

    sql = "UPDATE exerciselist SET approve = {} WHERE exid = {}".format(approve,exid)
    cur.execute(sql)
    conn.commit()
    cur.close()

    response['ok'] = 1
    return response


if __name__ == "__main__":
    # userSignup("lzy", '66666')
    print(addExercise('cat', 'question2', 'choice_A', 'choice_B',
                      '', '', 'answer', '28888'))
